{
 "cells": [
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import warnings\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from collections import Counter\n",
    "from sklearn import preprocessing\n",
    "\n",
    "#设置为seaborn风格\n",
    "sns.set()\n",
    "#不显示警告\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  #显示中文\n",
    "plt.rcParams['axes.unicode_minus'] = False  #用来正常显示负号"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:08.551282800Z",
     "start_time": "2024-03-15T06:36:08.407716400Z"
    }
   },
   "id": "c1d08af48f25b301",
   "execution_count": 201
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 用户特征\n",
    "## 用户基本特征\n",
    "获取基本的用户特征，基于用户本身属性多为类别特征的特点"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "7689483253aef165"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "users = pd.read_csv('./user_expand.csv')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:08.791454300Z",
     "start_time": "2024-03-15T06:36:08.554335700Z"
    }
   },
   "id": "1f06e3955d90b07f",
   "execution_count": 202
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:08.805311600Z",
     "start_time": "2024-03-15T06:36:08.792411500Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "user_ID              000089d6a6\nuser_level                    1\nfirst_order_month       2017-08\nplus                          0\ngender                        F\nage                           3\nmarital_status                S\neducation                     3\ncity_level                    4\npurchase_power                3\nis_click                   10.0\nis_order                    8.0\norder_click_ratio           0.8\nName: 0, dtype: object"
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 373835 entries, 0 to 373834\n",
      "Data columns (total 13 columns):\n",
      " #   Column             Non-Null Count   Dtype  \n",
      "---  ------             --------------   -----  \n",
      " 0   user_ID            373835 non-null  object \n",
      " 1   user_level         373835 non-null  int64  \n",
      " 2   first_order_month  373835 non-null  object \n",
      " 3   plus               373835 non-null  int64  \n",
      " 4   gender             373835 non-null  object \n",
      " 5   age                373835 non-null  int64  \n",
      " 6   marital_status     373835 non-null  object \n",
      " 7   education          373835 non-null  int64  \n",
      " 8   city_level         373835 non-null  int64  \n",
      " 9   purchase_power     373835 non-null  int64  \n",
      " 10  is_click           373835 non-null  float64\n",
      " 11  is_order           373835 non-null  float64\n",
      " 12  order_click_ratio  373835 non-null  float64\n",
      "dtypes: float64(3), int64(6), object(4)\n",
      "memory usage: 37.1+ MB\n"
     ]
    }
   ],
   "source": [
    "users.info()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:08.852866800Z",
     "start_time": "2024-03-15T06:36:08.806279200Z"
    }
   },
   "id": "f1f205368bc3c974",
   "execution_count": 204
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 转化数据形式\n",
    "- plus\n",
    "- age\n",
    "- JD_age_month\n",
    "- gender\n",
    "- user_level\n",
    "- age\n",
    "- marital_status\n",
    "- education\n",
    "- city_level\n",
    "- purchase_power"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "66c8524769057fb8"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# 确保无空值\n",
    "users.dropna(axis=0, how='any', inplace=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:08.915749700Z",
     "start_time": "2024-03-15T06:36:08.852324400Z"
    }
   },
   "id": "4fefa82489c77f65",
   "execution_count": 205
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "users['first_order_month'] = pd.to_datetime(users['first_order_month'], format='%Y-%m')\n",
    "end_date = pd.to_datetime('2018-03', format='%Y-%m')\n",
    "users['JD_age_months'] = (end_date.year - users['first_order_month'].dt.year) * 12 + (\n",
    "        end_date.month - users['first_order_month'].dt.month)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:08.993782Z",
     "start_time": "2024-03-15T06:36:08.916759900Z"
    }
   },
   "id": "84740b5e945aaff1",
   "execution_count": 206
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "user_ID                       000089d6a6\nuser_level                             1\nfirst_order_month    2017-08-01 00:00:00\nplus                                   0\ngender                                 F\nage                                    3\nmarital_status                         S\neducation                              3\ncity_level                             4\npurchase_power                         3\nis_click                            10.0\nis_order                             8.0\norder_click_ratio                    0.8\nJD_age_months                          7\nName: 0, dtype: object"
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.iloc[0]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.009184900Z",
     "start_time": "2024-03-15T06:36:08.995828800Z"
    }
   },
   "id": "bcadee10fc3bb168",
   "execution_count": 207
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "0          7\n1          0\n2         21\n3         45\n4         67\n          ..\n373830    37\n373831     0\n373832     4\n373833    23\n373834     5\nName: JD_age_months, Length: 373835, dtype: int64"
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users['JD_age_months']"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.033711Z",
     "start_time": "2024-03-15T06:36:09.009184900Z"
    }
   },
   "id": "f67306d70c6b76c6",
   "execution_count": 208
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "users = users[[\n",
    "    'user_ID', 'plus',\n",
    "    'gender',\n",
    "    'age',\n",
    "    'user_level',\n",
    "    'marital_status',\n",
    "    'education',\n",
    "    'city_level',\n",
    "    'purchase_power',\n",
    "    'JD_age_months']]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.055293800Z",
     "start_time": "2024-03-15T06:36:09.024294800Z"
    }
   },
   "id": "dd2ab8d421bd8b57",
   "execution_count": 209
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "user_ID           000089d6a6\nplus                       0\ngender                     F\nage                        3\nuser_level                 1\nmarital_status             S\neducation                  3\ncity_level                 4\npurchase_power             3\nJD_age_months              7\nName: 0, dtype: object"
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.iloc[0]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.069685300Z",
     "start_time": "2024-03-15T06:36:09.055293800Z"
    }
   },
   "id": "534a48fedac23fb1",
   "execution_count": 210
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "le = preprocessing.LabelEncoder()\n",
    "age_df = le.fit_transform(users['age'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.093657200Z",
     "start_time": "2024-03-15T06:36:09.072197700Z"
    }
   },
   "id": "5f3fc98496fa73b3",
   "execution_count": 211
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# 指定前缀 age\n",
    "age_df = pd.get_dummies(age_df, prefix='age')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.101216800Z",
     "start_time": "2024-03-15T06:36:09.086243400Z"
    }
   },
   "id": "7e86f0a0b385031a",
   "execution_count": 212
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "        age_0  age_1  age_2  age_3  age_4  age_5\n0           0      0      0      1      0      0\n1           1      0      0      0      0      0\n2           1      0      0      0      0      0\n3           0      0      0      1      0      0\n4           1      0      0      0      0      0\n...       ...    ...    ...    ...    ...    ...\n373830      0      0      0      1      0      0\n373831      1      0      0      0      0      0\n373832      0      0      0      0      1      0\n373833      0      0      0      1      0      0\n373834      0      0      0      1      0      0\n\n[373835 rows x 6 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>age_0</th>\n      <th>age_1</th>\n      <th>age_2</th>\n      <th>age_3</th>\n      <th>age_4</th>\n      <th>age_5</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>373830</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>373831</th>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>373832</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>373833</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>373834</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>373835 rows × 6 columns</p>\n</div>"
     },
     "execution_count": 213,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.120294600Z",
     "start_time": "2024-03-15T06:36:09.101216800Z"
    }
   },
   "id": "e95c1282034e1f07",
   "execution_count": 213
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "def get_dum(df_name):\n",
    "    return pd.get_dummies(users[df_name], prefix=df_name)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.147264600Z",
     "start_time": "2024-03-15T06:36:09.118291400Z"
    }
   },
   "id": "baaceb1c5edd087d",
   "execution_count": 214
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "plus_df = get_dum('plus')\n",
    "gender_df = get_dum('gender')\n",
    "user_level_df = get_dum('user_level')\n",
    "marital_df = get_dum('marital_status')\n",
    "education_df = get_dum('education')\n",
    "city_level_df = get_dum('city_level')\n",
    "purchase_power_df = get_dum('purchase_power')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.203831100Z",
     "start_time": "2024-03-15T06:36:09.133576600Z"
    }
   },
   "id": "1962f1bb893701a3",
   "execution_count": 215
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "user_ID_df = users['user_ID']\n",
    "JD_age_df = users['JD_age_months']"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.216156400Z",
     "start_time": "2024-03-15T06:36:09.196628Z"
    }
   },
   "id": "6243e8c85d3860c9",
   "execution_count": 216
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "users = pd.concat(\n",
    "    [user_ID_df, plus_df, gender_df, user_level_df, age_df, marital_df, education_df, city_level_df, purchase_power_df,\n",
    "     JD_age_df],\n",
    "    axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.230427200Z",
     "start_time": "2024-03-15T06:36:09.212081Z"
    }
   },
   "id": "e56877911165882a",
   "execution_count": 217
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "      user_ID  plus_0  plus_1  gender_F  gender_M  gender_U  user_level_-1  \\\n0  000089d6a6       1       0         1         0         0              0   \n1  0000babd1f       1       0         0         0         1              0   \n2  0000bc018b       1       0         1         0         0              0   \n3  0000d0e5ab       1       0         0         1         0              0   \n4  0000dce472       0       1         0         0         1              0   \n\n   user_level_0  user_level_1  user_level_2  ...  city_level_3  city_level_4  \\\n0             0             1             0  ...             0             1   \n1             0             1             0  ...             0             0   \n2             0             0             0  ...             0             0   \n3             0             0             0  ...             0             0   \n4             0             0             0  ...             0             0   \n\n   city_level_5  purchase_power_-1  purchase_power_1  purchase_power_2  \\\n0             0                  0                 0                 0   \n1             0                  1                 0                 0   \n2             0                  0                 0                 0   \n3             0                  0                 0                 1   \n4             0                  1                 0                 0   \n\n   purchase_power_3  purchase_power_4  purchase_power_5  JD_age_months  \n0                 1                 0                 0              7  \n1                 0                 0                 0              0  \n2                 1                 0                 0             21  \n3                 0                 0                 0             45  \n4                 0                 0                 0             67  \n\n[5 rows x 40 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>user_ID</th>\n      <th>plus_0</th>\n      <th>plus_1</th>\n      <th>gender_F</th>\n      <th>gender_M</th>\n      <th>gender_U</th>\n      <th>user_level_-1</th>\n      <th>user_level_0</th>\n      <th>user_level_1</th>\n      <th>user_level_2</th>\n      <th>...</th>\n      <th>city_level_3</th>\n      <th>city_level_4</th>\n      <th>city_level_5</th>\n      <th>purchase_power_-1</th>\n      <th>purchase_power_1</th>\n      <th>purchase_power_2</th>\n      <th>purchase_power_3</th>\n      <th>purchase_power_4</th>\n      <th>purchase_power_5</th>\n      <th>JD_age_months</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>000089d6a6</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>7</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0000babd1f</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0000bc018b</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>21</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0000d0e5ab</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>45</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0000dce472</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>67</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 40 columns</p>\n</div>"
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:09.243542900Z",
     "start_time": "2024-03-15T06:36:09.226944Z"
    }
   },
   "id": "da6ee5d3f74375e0",
   "execution_count": 218
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "users.to_csv('user_feature.csv', index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:36:10.247262Z",
     "start_time": "2024-03-15T06:36:09.243542900Z"
    }
   },
   "id": "c6aeb0c7fce370e1",
   "execution_count": 219
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 商品特征"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "b9ab63bff2580c01"
  },
  {
   "cell_type": "markdown",
   "source": [
    "- brand_size\n",
    "SKU数量可以在一定程度上表明品牌的产品多样性和市场覆盖范围。\n",
    "- type\n",
    "- attr1\n",
    "- attr2"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "1e4dd34ac6e35efe"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "skus = pd.read_csv('./sku_expand.csv')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:28.282532500Z",
     "start_time": "2024-03-15T06:46:28.243234700Z"
    }
   },
   "id": "76e42725fa345b0b",
   "execution_count": 243
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "       sku_ID  type    brand_ID attribute1 attribute2 activate_date  \\\n0  a234e08c57     1  c3ab4bf4d9        3.0       60.0           NaN   \n1  6449e1fd87     1  1d8b4b4c63        2.0       50.0           NaN   \n2  09b70fcd83     2  eb7d2a675a        3.0       70.0           NaN   \n3  acad9fed04     2  9b0d3a5fc6        3.0       70.0           NaN   \n4  2fa77e3b4d     2  b681299668          -          -           NaN   \n\n  deactivate_date  is_click  is_order  order_click_ratio  \n0             NaN       644        74           0.114907  \n1             NaN       871       123           0.141217  \n2             NaN      4860       699           0.143827  \n3             NaN      5298       222           0.041903  \n4             NaN      2360       139           0.058898  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>sku_ID</th>\n      <th>type</th>\n      <th>brand_ID</th>\n      <th>attribute1</th>\n      <th>attribute2</th>\n      <th>activate_date</th>\n      <th>deactivate_date</th>\n      <th>is_click</th>\n      <th>is_order</th>\n      <th>order_click_ratio</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a234e08c57</td>\n      <td>1</td>\n      <td>c3ab4bf4d9</td>\n      <td>3.0</td>\n      <td>60.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>644</td>\n      <td>74</td>\n      <td>0.114907</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>6449e1fd87</td>\n      <td>1</td>\n      <td>1d8b4b4c63</td>\n      <td>2.0</td>\n      <td>50.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>871</td>\n      <td>123</td>\n      <td>0.141217</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>09b70fcd83</td>\n      <td>2</td>\n      <td>eb7d2a675a</td>\n      <td>3.0</td>\n      <td>70.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>4860</td>\n      <td>699</td>\n      <td>0.143827</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>acad9fed04</td>\n      <td>2</td>\n      <td>9b0d3a5fc6</td>\n      <td>3.0</td>\n      <td>70.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>5298</td>\n      <td>222</td>\n      <td>0.041903</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2fa77e3b4d</td>\n      <td>2</td>\n      <td>b681299668</td>\n      <td>-</td>\n      <td>-</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2360</td>\n      <td>139</td>\n      <td>0.058898</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 244,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skus.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:28.631106800Z",
     "start_time": "2024-03-15T06:46:28.577270600Z"
    }
   },
   "id": "9060d7220ab0ba47",
   "execution_count": 244
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "skus = skus[['sku_ID', 'type', 'brand_ID', 'attribute1', 'attribute2']]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:28.901091Z",
     "start_time": "2024-03-15T06:46:28.884025500Z"
    }
   },
   "id": "c8330387b83bdbaf",
   "execution_count": 245
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "       sku_ID  type    brand_ID attribute1 attribute2\n0  a234e08c57     1  c3ab4bf4d9        3.0       60.0\n1  6449e1fd87     1  1d8b4b4c63        2.0       50.0\n2  09b70fcd83     2  eb7d2a675a        3.0       70.0\n3  acad9fed04     2  9b0d3a5fc6        3.0       70.0\n4  2fa77e3b4d     2  b681299668          -          -",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>sku_ID</th>\n      <th>type</th>\n      <th>brand_ID</th>\n      <th>attribute1</th>\n      <th>attribute2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a234e08c57</td>\n      <td>1</td>\n      <td>c3ab4bf4d9</td>\n      <td>3.0</td>\n      <td>60.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>6449e1fd87</td>\n      <td>1</td>\n      <td>1d8b4b4c63</td>\n      <td>2.0</td>\n      <td>50.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>09b70fcd83</td>\n      <td>2</td>\n      <td>eb7d2a675a</td>\n      <td>3.0</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>acad9fed04</td>\n      <td>2</td>\n      <td>9b0d3a5fc6</td>\n      <td>3.0</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2fa77e3b4d</td>\n      <td>2</td>\n      <td>b681299668</td>\n      <td>-</td>\n      <td>-</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 246,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skus.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:29.191162400Z",
     "start_time": "2024-03-15T06:46:29.160987700Z"
    }
   },
   "id": "2537413071610eb0",
   "execution_count": 246
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "brand_counts = skus['brand_ID'].value_counts(dropna=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:29.438859300Z",
     "start_time": "2024-03-15T06:46:29.418852800Z"
    }
   },
   "id": "b5ad445705d21711",
   "execution_count": 247
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "198cec62a1    1231\nbd97f9a5fa    1132\nadbd559b78     952\n9b0d3a5fc6     831\n42e6445fca     627\n              ... \nd3ea7c7720       1\n3c966f9e6d       1\ne475dc56c6       1\nbfab8abd7a       1\n65c76167e3       1\nName: brand_ID, Length: 1890, dtype: int64"
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_counts"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:29.801543200Z",
     "start_time": "2024-03-15T06:46:29.769279400Z"
    }
   },
   "id": "533863dfb4f98f87",
   "execution_count": 248
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "quantile_25 = brand_counts.quantile(0.25)\n",
    "quantile_75 = brand_counts.quantile(0.75)\n",
    "quantile_95 = brand_counts.quantile(0.95)\n",
    "\n",
    "\n",
    "def classify_brand(count):\n",
    "    if count >= quantile_95:\n",
    "        return '3'\n",
    "    elif count >= quantile_75:\n",
    "        return '2'\n",
    "    elif count >= quantile_25:\n",
    "        return '1'\n",
    "    else:\n",
    "        return '0'\n",
    "\n",
    "\n",
    "brand_type = brand_counts.apply(classify_brand)\n",
    "brand_type_df = brand_type.reset_index()\n",
    "brand_type_df.columns = ['brand_ID', 'brand_type']\n",
    "\n",
    "# 将 brand_type_df DataFrame 根据 brand_ID 与原始 skus DataFrame 合并\n",
    "skus = pd.merge(skus, brand_type_df, on='brand_ID', how='left')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:30.257039200Z",
     "start_time": "2024-03-15T06:46:30.229631500Z"
    }
   },
   "id": "6b33452d6c5fadbd",
   "execution_count": 249
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "       sku_ID  type    brand_ID attribute1 attribute2 brand_type\n0  a234e08c57     1  c3ab4bf4d9        3.0       60.0          1\n1  6449e1fd87     1  1d8b4b4c63        2.0       50.0          2\n2  09b70fcd83     2  eb7d2a675a        3.0       70.0          2\n3  acad9fed04     2  9b0d3a5fc6        3.0       70.0          3\n4  2fa77e3b4d     2  b681299668          -          -          3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>sku_ID</th>\n      <th>type</th>\n      <th>brand_ID</th>\n      <th>attribute1</th>\n      <th>attribute2</th>\n      <th>brand_type</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a234e08c57</td>\n      <td>1</td>\n      <td>c3ab4bf4d9</td>\n      <td>3.0</td>\n      <td>60.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>6449e1fd87</td>\n      <td>1</td>\n      <td>1d8b4b4c63</td>\n      <td>2.0</td>\n      <td>50.0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>09b70fcd83</td>\n      <td>2</td>\n      <td>eb7d2a675a</td>\n      <td>3.0</td>\n      <td>70.0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>acad9fed04</td>\n      <td>2</td>\n      <td>9b0d3a5fc6</td>\n      <td>3.0</td>\n      <td>70.0</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2fa77e3b4d</td>\n      <td>2</td>\n      <td>b681299668</td>\n      <td>-</td>\n      <td>-</td>\n      <td>3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skus.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:30.819256600Z",
     "start_time": "2024-03-15T06:46:30.785560500Z"
    }
   },
   "id": "bbac067061d8f536",
   "execution_count": 250
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "brand_type_df = pd.get_dummies(skus['brand_type'], prefix='brand_type')\n",
    "type_df = pd.get_dummies(skus['type'], prefix='type')\n",
    "sku_ID_df = skus['sku_ID']\n",
    "skus_attr1 = skus['attribute1']\n",
    "skus_attr2 = skus['attribute2']"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:31.374811800Z",
     "start_time": "2024-03-15T06:46:31.352325200Z"
    }
   },
   "id": "6fe67624b1259425",
   "execution_count": 251
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "skus_feature = pd.concat([sku_ID_df, brand_type_df, type_df, skus_attr1, skus_attr2], axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:32.287267800Z",
     "start_time": "2024-03-15T06:46:32.260278Z"
    }
   },
   "id": "be2f7f204cb67665",
   "execution_count": 252
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "           sku_ID  brand_type_1  brand_type_2  brand_type_3  type_1  type_2  \\\n0      a234e08c57             1             0             0       1       0   \n1      6449e1fd87             0             1             0       1       0   \n2      09b70fcd83             0             1             0       0       1   \n3      acad9fed04             0             0             1       0       1   \n4      2fa77e3b4d             0             0             1       0       1   \n...           ...           ...           ...           ...     ...     ...   \n31863  121d8470d2             0             0             1       0       1   \n31864  e41c62189d             0             0             1       0       1   \n31865  01d16f7678             0             0             1       0       1   \n31866  83fc55d93b             0             0             1       0       1   \n31867  c1b1a4b058             1             0             0       0       1   \n\n      attribute1 attribute2  \n0            3.0       60.0  \n1            2.0       50.0  \n2            3.0       70.0  \n3            3.0       70.0  \n4              -          -  \n...          ...        ...  \n31863        3.0          -  \n31864          -          -  \n31865          -          -  \n31866          -          -  \n31867          -          -  \n\n[31868 rows x 8 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>sku_ID</th>\n      <th>brand_type_1</th>\n      <th>brand_type_2</th>\n      <th>brand_type_3</th>\n      <th>type_1</th>\n      <th>type_2</th>\n      <th>attribute1</th>\n      <th>attribute2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a234e08c57</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>3.0</td>\n      <td>60.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>6449e1fd87</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2.0</td>\n      <td>50.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>09b70fcd83</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>3.0</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>acad9fed04</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>3.0</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2fa77e3b4d</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>-</td>\n      <td>-</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>31863</th>\n      <td>121d8470d2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>3.0</td>\n      <td>-</td>\n    </tr>\n    <tr>\n      <th>31864</th>\n      <td>e41c62189d</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>-</td>\n      <td>-</td>\n    </tr>\n    <tr>\n      <th>31865</th>\n      <td>01d16f7678</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>-</td>\n      <td>-</td>\n    </tr>\n    <tr>\n      <th>31866</th>\n      <td>83fc55d93b</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>-</td>\n      <td>-</td>\n    </tr>\n    <tr>\n      <th>31867</th>\n      <td>c1b1a4b058</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>-</td>\n      <td>-</td>\n    </tr>\n  </tbody>\n</table>\n<p>31868 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skus_feature"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:46:32.763749400Z",
     "start_time": "2024-03-15T06:46:32.747493400Z"
    }
   },
   "id": "3fb494608d063f33",
   "execution_count": 253
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "skus_feature.to_csv('skus_feature.csv', index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-15T06:48:20.703151Z",
     "start_time": "2024-03-15T06:48:20.634441800Z"
    }
   },
   "id": "9ada9cb7e338b772",
   "execution_count": 254
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   },
   "id": "dc36054f0ed8490a"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
